using System;
using System.Data;
using System.IO;
using Newtonsoft.Json;
using OfficeOpenXml;
using UnityEngine;

namespace Tools.ExcelToJson.Editor
{
    // ReSharper disable once InconsistentNaming
    internal static class EPPlusUtils
    {
        public static ExcelPackage Open(string filePath)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            var result = new ExcelPackage();
            using (var stream = File.OpenRead(filePath))
                result.Load(stream);
            return result;
        }
        
        private static void ReadXls(string filePath)
        {
            var existingFile = new FileInfo(filePath);
            var package = new ExcelPackage(existingFile);
            //get the first worksheet in the workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
            int colCount = worksheet.Dimension.End.Column; //get Column Count
            int rowCount = worksheet.Dimension.End.Row; //get row count
            for (int row = 1; row <= rowCount; row++)
            {
                for (int col = 1; col <= colCount; col++)
                {
                    Debug.Log(" Row:" + row + " column:" + col + " Value:" +
                              worksheet.Cells[row, col].Value?.ToString().Trim());
                }
            }

            package.Dispose();
        }

        public static T ReadFromExcel<T>(string path, bool hasHeader = true)
        {
            var excelPack = new ExcelPackage();

            //Load excel stream
            using (var stream = File.OpenRead(path))
            {
                excelPack.Load(stream);
            }

            //处理第一个工作表。(如果处理多个表格，可以在此用for循环处理)
            var ws = excelPack.Workbook.Worksheets[0];
            DataTable excelasTable = new DataTable();
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                if (!string.IsNullOrEmpty(firstRowCell.Text))
                {
                    string firstColumn = string.Format("Column {0}", firstRowCell.Start.Column);
                    excelasTable.Columns.Add(hasHeader ? firstRowCell.Text : firstColumn);
                }
            }

            var startRow = hasHeader ? 2 : 1;
            for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, excelasTable.Columns.Count];
                DataRow row = excelasTable.Rows.Add();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
            }

            Debug.Log(JsonConvert.SerializeObject(excelasTable));
            //将所有内容作为泛型获取，最终定是否强制转换为所需类型
            var generatedType = JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(excelasTable));
            excelPack.Dispose();
            return (T) Convert.ChangeType(generatedType, typeof(T));
        }
    }
}